﻿using System;

namespace ConsoleTest
{
    public class ADONETTest
    {
        /// <summary>
        /// 表之间关系演示
        /// </summary>
        public static void DataRelationTest()
        {
            System.Data.DataSet ds = new System.Data.DataSet();
            using (System.Data.SqlClient.SqlConnection SqlConn = new System.Data.SqlClient.SqlConnection
            ("server=localhost; database=HSEDB; Integrated Security=true;"))
            {
                using (System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter())
                {
                    da.SelectCommand = new System.Data.SqlClient.SqlCommand("select * from t_cunit",SqlConn);
                    da.Fill(ds, "Cunit");
                    da.SelectCommand = new System.Data.SqlClient.SqlCommand("select * from rt_unitprj", SqlConn);
                    da.Fill(ds, "rt");

                }
            }

            System.Data.DataColumn dCol1=ds.Tables["Cunit"].Columns["cunit_id"];
            System.Data.DataColumn dCol2=ds.Tables["rt"].Columns["cunit_id"];
            System.Data.DataRelation dRelation = ds.Relations.Add(dCol1, dCol2);

            foreach(System.Data.DataRow dr1 in ds.Tables["Cunit"].Rows)
            {
                System.Console.WriteLine("Cunit:{0}",dr1["cunit_name"]);
                foreach(System.Data.DataRow dr2 in dr1.GetChildRows(dRelation))
                {
                    System.Console.WriteLine("{0}{0}",dr2["cunit_id"],dr2["unit_id"]);
                }
            }
        }
        /// <summary>
        /// DataSet或者DataTable的AcceptChanges方法只是设置内存中数据行
        /// 的DataRowState属性，并没有引起数据库数据的更新
        /// 需要调用Update方法进行更新；
        ///  System.Data.SqlClient.SqlCommandBuilder能根据查询字符串推测生成更新语句
        /// </summary>
        public static void FillAndChange()
        {
            System.Data.DataSet ds = new System.Data.DataSet();
            string StrConn="server=localhost; database=HSEDB; Integrated Security=true;";
            string StrComm="select * from t_unit";
            using (System.Data.SqlClient.SqlConnection SqlConn = new System.Data.SqlClient.SqlConnection
            (StrConn))
            {
                using (System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter())
                {
                    da.SelectCommand = new System.Data.SqlClient.SqlCommand(StrComm,SqlConn);
                    da.Fill(ds, "unit");
                }
            }
            
            ds.Tables["unit"].Rows.Add(44,"test",0);
            
            using(System.Data.SqlClient.SqlDataAdapter da=new System.Data.SqlClient.SqlDataAdapter(StrComm,StrConn))
            {
                System.Data.SqlClient.SqlCommandBuilder cmd = new System.Data.SqlClient.SqlCommandBuilder(da);
                try
                {
                    da.Update(ds.Tables["unit"]);
                }
                catch(Exception e)
                {
                    System.Console.WriteLine("ERROR:"+e.Message);
                }
            }


        }

        /// <summary>
        /// 强类型DataSet测试
        /// </summary>
        public static void TypedDataSetTest()
        {
            HSEDBDataSet ds = new HSEDBDataSet();
            using (System.Data.SqlClient.SqlConnection cnx = new System.Data.SqlClient.SqlConnection
            ("server=localhost; database=HSEDB;Integrated Security=true"))
            {
                using (System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter())
                {
                    da.SelectCommand =new System.Data.SqlClient.SqlCommand( "select * from t_cunit",cnx);
                    da.Fill(ds, "t_cunit");
                    da.SelectCommand = new System.Data.SqlClient.SqlCommand("select * from rt_unitprj", cnx);
                    da.Fill(ds, "rt_unitprj");
                }
            }

            foreach (HSEDBDataSet.t_cunitRow row1 in ds.t_cunit)
            {
                System.Console.WriteLine("Cunit:" + row1.cunit_name);
                foreach (HSEDBDataSet.rt_unitprjRow row2 in row1.Getrt_unitprjRows())
                {
                    System.Console.WriteLine("   "+row2.cunit_id+" "+row2.unit_id+"   "+row2.t_cunitRow.cunit_name);
                }
            }
        }
        
        /// <summary>
        /// 强类型DataSet自定义方法测试
        /// </summary>
        public static void TypedDataSetQuery()
        {
            HSEDBDataSet.t_cunitDataTable table = new HSEDBDataSet.t_cunitDataTable();
            using (System.Data.SqlClient.SqlConnection cnx = new System.Data.SqlClient.SqlConnection
            ("server=localhost; database=HSEDB;Integrated Security=true"))
            {
                using (HSEDBDataSetTableAdapters.t_cunitTableAdapter typedDa = new ConsoleTest.HSEDBDataSetTableAdapters.t_cunitTableAdapter())
                {
                    table = typedDa.GetCunitById(5);


                    table.WriteXml("t_cunit.xml");
                }
            }

            foreach (HSEDBDataSet.t_cunitRow row in table)
            {
                System.Console.WriteLine(row.cunit_name+"  "+row.prj_name+"  "+row.prj_begtime+"   "+row.prj_endtime);
                
            }

            //类型化DataSet与DataAdapter 提供了用户透明的数据更改等方法
            using ( HSEDBDataSetTableAdapters.t_cunitTableAdapter da = new ConsoleTest.HSEDBDataSetTableAdapters.t_cunitTableAdapter())
            {
                foreach (HSEDBDataSet.t_cunitRow row in table)
                {
                    row.prj_endtime = System.DateTime.Now;
                 }
                da.Update(table);
             }

        }
    }
}